package com.stephen.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;

/**
 * Created by stephen on 2021-03-07 11:56 .
 * Description: 03版与07版,导出,只是workbook的实现类不同,07生成Excel的后缀名多了一个x
 * HSSFWorkbook  # 03版本,写数据快
 * XSSFWorkbook  # 07版本,写数据慢
 * SXSSFWorkbook # 07增强版本,写数据速度快,占用更少内存,过程中会产生临时文件,需要清理临时文件
 */
public class ExcelWriteTest {
    static String PATH = "D:\\IDEA_Projects\\java-excel-demo\\poi\\src\\main\\java\\com\\stephen\\poi";

    /**
     * 03版本,导出Excel简单demo
     * 导出速度较快
     * 但最多只能写入65536行
     */
    @Test
    public void Test03() throws Exception {
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建sheet
        Sheet sheet = workbook.createSheet();
        // 创建行
        Row row1 = sheet.createRow(0);
        // 创建列
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列");
        // 创建列
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("第一行第二列");

        // 创建行
        Row row2 = sheet.createRow(1);
        // 创建列
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列");
        // 创建列
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("第二行第二列");

        FileOutputStream outputStream = new FileOutputStream(PATH + "03.xls");
        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * 07版本,导出
     * 行数没有限制，但导出的速度较慢
     */
    @Test
    public void Test07() throws Exception {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建sheet
        Sheet sheet = workbook.createSheet();
        // 创建行
        Row row1 = sheet.createRow(0);
        // 创建列
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列");
        // 创建列
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("第一行第二列");

        // 创建行
        Row row2 = sheet.createRow(1);
        // 创建列
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列");
        // 创建列
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("第二行第二列");

        FileOutputStream outputStream = new FileOutputStream(PATH + "07.xlsx");
        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * 03版本大数据量导出，65536行,耗时2秒
     * 超过65536行会报错:Invalid row number (65536) outside allowable range
     */
    @Test
    public void Test03BigData() throws Exception{
        Long start = System.currentTimeMillis();
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "test03BigData.xls");
        workbook.write(outputStream);
        outputStream.close();
        Long end = System.currentTimeMillis();
        System.out.println("耗时" + (end - start) / 1000 + "秒");
    }

    /**
     * 07版本大数据量导出
     * 耗时较长,无行数限制,65536行耗时8s
     */
    @Test
    public void Test07BigData() throws Exception{
        Long start = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum < 65537; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        FileOutputStream outputStream = new FileOutputStream(PATH + "test07BigData.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        Long end = System.currentTimeMillis();
        System.out.println("耗时" + (end - start) / 1000 + "秒");
    }

    /**
     * 07增强版本大数据量导出
     * 耗时较短,无行数限制,65536行耗时3秒
     * 默认每次写100条记录到内存中,超过这个数量,则最前面的数据被写入临时文件
     * 可以自定义内存中数据的数量,new SXSSFWorkbook(数量);
     * 仍然可能会消耗大量内存
     */
    @Test
    public void Test07BigDataS() throws Exception{
        Long start = System.currentTimeMillis();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum < 65537; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        FileOutputStream outputStream = new FileOutputStream(PATH + "test07BigDataS.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        Long end = System.currentTimeMillis();
        System.out.println("耗时" + (end - start) / 1000 + "秒");
    }

}
